﻿using System;
using System.Collections.Generic;

using System.Text;
using Estimate.Common.DBFieldTypes;
using Estimate.OldSemiCompare.Entity;
using System.Data.SqlClient;
using Estimate.Common;
using System.Text.RegularExpressions;

namespace Estimate.OldSemiCompare.Repository
{
    /// <summary>
    /// 对CityFore, SiteFore,ZytCityFore,RainFore表进行操作的基础类
    /// 由于几张表的表结构完全一致，所以使用基础类进行处理
    /// 
    /// </summary>
    public class ForeRepository : BaseRepository
    {

        public ForeRepository(string connectionString, string tableName) :base(connectionString,tableName)
        {
        }

        public override string GetCreateTableSql()
        {
            string sqlcreate = String.Format("CREATE TABLE [{0}](id bigint identity(1,1), [date] char(8) not null, clock varchar(8) not null, span varchar(9) not null, sendpoint varchar(8) not null, type varchar(8) not null, semitype varchar(8) not null, [value] text, semi_old text, semi_new text ) \n", _tableName);
            sqlcreate += String.Format("CREATE CLUSTERED INDEX [{0}_INDEX] ON [{1}] (date,span,type,semitype,clock,sendpoint) \n", _tableName, _tableName);
            sqlcreate += String.Format("alter table [{0}] add primary key (id)", _tableName);
            return sqlcreate;
        }

        /// <summary>
        /// 更新预处理结果
        /// 如果用户改变了 semitype, 则需要新建一条记录
        /// </summary>
        /// <param name="foreEntity">数据库实例对象</param>
        public void UpdateSemiAndValue(ForeEntity foreEntity, bool isOld)
        {
            string sql = "";
            if( isOld)
                sql = String.Format("UPDATE [{0}] SET semi_old=@semi_old, value=@value WHERE date=@date  and span=@span  and type=@type and  semitype=@semitype and  clock=@clock and sendpoint=@sendpoint ", _tableName);
            else
                sql = String.Format("UPDATE [{0}] SET semi_new=@semi_new, value=@value WHERE date=@date  and span=@span  and type=@type and  semitype=@semitype and  clock=@clock and sendpoint=@sendpoint ", _tableName);
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@date", foreEntity.Date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@clock", foreEntity.Clock.ToString());
            param[2] = new SqlParameter("@span", foreEntity.Span.ToString());
            param[3] = new SqlParameter("@type", foreEntity.Type.ToString());
            param[4] = new SqlParameter("@semitype", foreEntity.Semitype.ToString());
            param[5] = new SqlParameter("@sendpoint", foreEntity.Sendpoint.ToString());
            param[6] = new SqlParameter("@semi_old", foreEntity.Semi_old);
            if(isOld)
                param[7] = new SqlParameter("@semi_old", foreEntity.Semi_old);
            else
                param[6] = new SqlParameter("@semi_new", foreEntity.Semi_new);
            param[7] = new SqlParameter("@value", foreEntity.Value);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        /// <summary>
        /// 新增一条记录
        /// </summary>
        /// <param name="foreEntity">数据库实例对象</param>
        public void InsertForeRecord(ForeEntity foreEntity, bool isOld)
        {
            string sql = "";
            if (isOld)
               sql = String.Format("INSERT INTO [{0}] (date,span,type,semitype,clock,sendpoint,value,semi_old) values(@date ,@span ,@type ,@semitype ,@clock,@sendpoint ,@value,@semi_old)", _tableName);
            else
                sql = String.Format("INSERT INTO [{0}] (date,span,type,semitype,clock,sendpoint,value,semi_new) values(@date ,@span ,@type ,@semitype ,@clock,@sendpoint ,@value,@semi_new)", _tableName);
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@date", foreEntity.Date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@clock", foreEntity.Clock.ToString());
            param[2] = new SqlParameter("@span", foreEntity.Span.ToString());
            param[3] = new SqlParameter("@type", foreEntity.Type.ToString());
            param[4] = new SqlParameter("@semitype", foreEntity.Semitype.ToString());
            param[5] = new SqlParameter("@sendpoint", foreEntity.Sendpoint.ToString());
            if (isOld)
                param[6] = new SqlParameter("@semi_old", foreEntity.Semi_old);
            else
                param[6] = new SqlParameter("@semi_new", foreEntity.Semi_new);
            param[7] = new SqlParameter("@value", foreEntity.Value);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        /// <summary>
        /// 给定查询条件，获取预测值
        /// </summary>
        /// <param name="foreEntity">数据记录</param>
        /// <returns></returns>
        public virtual ForeEntity GetForeRecord(DateTime date, ForeClockType clock, ForeSpanType span, ForeSendpointType sendpoint, ForeFactorType type, ForeSemiType semitype)
        {
            string sql = String.Format("SELECT id,value,semi FROM [{0}] WHERE date=@date  and span=@span  and type=@type and  semitype=@semitype  and clock=@clock and  sendpoint=@sendpoint", _tableName);
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@date", date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@clock", clock.ToString());
            param[2] = new SqlParameter("@span", span.ToString());
            param[3] = new SqlParameter("@type", type.ToString());
            param[4] = new SqlParameter("@semitype", semitype.ToString());
            param[5] = new SqlParameter("@sendpoint", sendpoint.ToString());

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql,param);
            if(reader.Read())
            {
                int id = Convert.ToInt32(reader["id"].ToString());
                string value =  reader["value"].ToString();
                string semi_old = reader["semi_old"].ToString();
                string semi_new = reader["semi_new"].ToString();
                connection.Close();
                ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi_old, semi_new);
                return entity;                
            }
            return null;
        }

        /// <summary>
        /// 设置一条记录，如果不存在，就会新增。否则会更新
        /// </summary>
        /// <param name="foreEntity">数据库实例对象</param>
        public virtual void SetForeRecord(ForeEntity foreEntity, bool isOld)
        {
            if (this.IsForeRecordExist(foreEntity.Date,foreEntity.Clock,foreEntity.Span,foreEntity.Sendpoint,foreEntity.Type, foreEntity.Semitype))
            {
                this.UpdateSemiAndValue(foreEntity, isOld);
            }
            else
            {
                this.InsertForeRecord(foreEntity, isOld);
            }
        }

        /// <summary>
        /// 检查数据库中是否已经存在一条记录。
        /// 判断条件就是 date,clock,span,type,semitype,sendpoint 这几个字段
        /// </summary>
        /// <param name="foreEntity"></param>
        /// <returns></returns>
        public virtual bool IsForeRecordExist(DateTime date, ForeClockType clock, ForeSpanType span, ForeSendpointType sendpoint, ForeFactorType type, ForeSemiType semitype)
        {
            string sql = String.Format("SELECT * FROM [{0}] WHERE date=@date  and span=@span  and type=@type and  semitype=@semitype  and clock=@clock  and  sendpoint=@sendpoint", _tableName);
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@date", date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@clock", clock.ToString());
            param[2] = new SqlParameter("@span", span.ToString());
            param[3] = new SqlParameter("@type", type.ToString());
            param[4] = new SqlParameter("@semitype", semitype.ToString());
            param[5] = new SqlParameter("@sendpoint", sendpoint.ToString());
            return DBHelper.Exists(_connectionString, sql, param);
        }

        
        /// <summary>
        /// 给定日期范围及查询条件，获取相应的所有semi值
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        public virtual ForeEntity[] InquerySemiWithDateRange(DateTime start, DateTime end, ForeFactorType type, ForeSemiType semitype)
        {
            List<ForeEntity> result = new List<ForeEntity>();

            string sql = String.Format("SELECT * FROM [{0}] WHERE date>='{1}' and date<='{2}' and type='{3}' and  semitype='{4}'",
                                        new object[] { _tableName, 
                                        start.Date.ToString("yyyyMMdd"), 
                                        end.Date.ToString("yyyyMMdd"),
                                        type.ToString(),
                                        semitype.ToString()
                                       });


            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
            while (reader.Read())
            {
                string value = reader["value"].ToString();
                string semi_old = reader["semi_old"].ToString();
                string semi_new = reader["semi_new"].ToString();
                DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                ForeClockType clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                ForeSpanType span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi_old, semi_new);
                result.Add(entity);
            }
            connection.Close();
            return result.ToArray();

        }      

        /// <summary>
        /// 给定日期范围及查询条件，获取相应的所有semi值
        /// </summary>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        public virtual ForeEntity[] InquerySemiWithDateRange(DateTime start, DateTime end, ForeClockType[] clocks, ForeSpanType span, ForeSendpointType[] sendpoints, ForeFactorType type, ForeSemiType semitype, bool needCache)
        {
                List<ForeEntity> result = new List<ForeEntity>();

                //将clock转化为string
                string[] strClock = new string[clocks.Length];
                for (int i = 0; i < clocks.Length; i++)
                {
                    strClock[i] = clocks[i].ToString();
                }
                string[] strSendpoint = new string[sendpoints.Length];
                for (int i = 0; i < sendpoints.Length; i++)
                {
                    strSendpoint[i] = sendpoints[i].ToString();
                }
                string sql = String.Format("SELECT * FROM [{0}] WHERE date>='{1}' and date<='{2}' and span='{3}'  and type='{4}' and  semitype='{5}'  and clock in ({6}) and  sendpoint in ({7})",
                    new object[] { _tableName, 
                    start.Date.ToString("yyyyMMdd"), 
                    end.Date.ToString("yyyyMMdd"),
                    
                    span.ToString(),
                    type.ToString(),
                     semitype.ToString(),
                     string.Format("'{0}'", string.Join("','", strClock)),
                      string.Format("'{0}'", string.Join("','", strSendpoint))
                     
                    });


                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                {
                    string value = reader["value"].ToString();
                    string semi_old = reader["semi_old"].ToString();
                    string semi_new = reader["semi_new"].ToString();
                    DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                    ForeClockType clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                    ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi_old, semi_new);
                    result.Add(entity);
                }
                connection.Close();
                return result.ToArray();
            
        }

        /// <summary>
        /// 给定查询条件，获取预测值
        /// </summary>
        /// <param name="foreEntity">数据记录</param>
        /// <returns></returns>
        public virtual ForeEntity[] GetForeRecordWithDate(DateTime select_date, ForeClockType[] clocks, ForeSendpointType[] sendpoints, ForeFactorType factor, ForeSemiType semitype, bool needCache)
        {
            
                string sql = String.Format("SELECT * FROM [{0}] WHERE date = '{1}'  and span <> ''  and type = '{2}' and  semitype='{3}'  and clock in ({4}) and  sendpoint in ({5}) ORDER BY sendpoint, span",
                    new object[]{
                    _tableName,
                    select_date.ToString("yyyyMMdd"),
                    factor.ToString(),
                    semitype.ToString(),
                    this.makeClocksDBIn( clocks),
                    this.makeSendpointsDBIn( sendpoints)
                });
                List<ForeEntity> result = new List<ForeEntity>();
                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
                while (reader.Read())
                {
                    string value = reader["value"].ToString();
                    string semi_old = reader["semi_old"].ToString();
                    string semi_new = reader["semi_new"].ToString();
                    DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                    ForeClockType clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                    ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                    ForeSpanType span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                    ForeFactorType type = (ForeFactorType)Enum.Parse(typeof(ForeFactorType), reader["type"].ToString());
                    ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi_old, semi_new);
                    result.Add(entity);
                }
                connection.Close();
                
                return result.ToArray();
        }

        public void DeleteRecordWithDateRange(DateTime begin, DateTime end)     
        {
            string sql = String.Format("DELETE FROM [{0}] WHERE date >= '{1}' and date <='{2}'",
               new object[]{
                    _tableName,
                    begin.ToString("yyyyMMdd"),
                    end.ToString("yyyyMMdd")
                });
            DBHelper.ExecuteSql(_connectionString, sql);
        }

        /// <summary>
        /// 给定查询条件，获取预测值
        /// </summary>
        /// <param name="foreEntity">数据记录</param>
        /// <returns></returns>
        public virtual ForeEntity[] GetForeRecordWithDateRange(DateTime beginDate, DateTime endDate, ForeClockType[] clocks, ForeSpanType[] spans, ForeSendpointType[] sendpoints, ForeFactorType[] types, ForeSemiType semitype, bool needCache)
        {
            //引入 memcached 缓存机制
            List<object> ps = new List<object>() { beginDate, endDate, semitype, this._tableName, "GetForeRecordWithDateRange2" };
            foreach (ForeClockType clock in clocks)
            {
                ps.Add(clock);
            }
            foreach (ForeSendpointType sendpoint in sendpoints)
            {
                ps.Add(sendpoint);
            }
            foreach (ForeSpanType span in spans)
            {
                ps.Add(span);
            }
            foreach (ForeFactorType type in types)
            {
                ps.Add(type);
            }
           
            string sql = String.Format("SELECT * FROM [{0}] WHERE date >= '{1}' and date <='{2}'  and span in ({3})  and type in ({4}) and  semitype='{5}'  and clock in ({6}) and  sendpoint in ({7})",
                new object[]{
                _tableName,
                beginDate.ToString("yyyyMMdd"),endDate.ToString("yyyyMMdd"),
                this.makeSpanDBIn(spans),
                this.makeFactorDBIn(types),
                semitype.ToString(),
                this.makeClocksDBIn( clocks), this.makeSendpointsDBIn( sendpoints)
            });
            List<ForeEntity> result = new List<ForeEntity>();
            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);
            while (reader.Read())
            {
                string value = reader["value"].ToString();
                string semi_old = reader["semi_old"].ToString();
                string semi_new = reader["semi_new"].ToString();
                DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                ForeClockType clock = (ForeClockType)Enum.Parse(typeof(ForeClockType), reader["clock"].ToString());
                ForeSendpointType sendpoint = (ForeSendpointType)Enum.Parse(typeof(ForeSendpointType), reader["sendpoint"].ToString());
                ForeSpanType span = (ForeSpanType)Enum.Parse(typeof(ForeSpanType), reader["span"].ToString());
                ForeFactorType type = (ForeFactorType)Enum.Parse(typeof(ForeFactorType), reader["type"].ToString());
                ForeEntity entity = new ForeEntity(date, clock, span, type, semitype, sendpoint, value, semi_old, semi_new);
                entity.Id = Convert.ToInt32(reader["id"].ToString());
                result.Add(entity);
            }
            connection.Close();
                
            return result.ToArray();

        }
      
    }
}
